{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "af6c35bf",
   "metadata": {},
   "source": [
    "# 浙教必修二 4.2搭建信息系统\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c9821ea3",
   "metadata": {},
   "source": [
    "> 行空板本身相当于一台能接入开源硬件的linux电脑，因此可以将服务器端及传感器数据发送都在行空板上使用纯Python代码来实现。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a4b7bef",
   "metadata": {},
   "source": [
    "# 使用的技术点"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "049dc876",
   "metadata": {},
   "source": [
    "- flask库实现web服务器的搭建\n",
    "- sqlite库实现数据库的操作\n",
    "- request库实现数据的传输\n",
    "- pinpong库实现传感器数据的读取\n",
    "- unihiker库实现行空板屏幕的显示"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4bfcded",
   "metadata": {},
   "source": [
    "# 操作步骤"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "230dd0c1",
   "metadata": {},
   "source": [
    "## 1-数据库初始化"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d853157",
   "metadata": {},
   "source": [
    "首先运行一次如下代码初始化sqlite库，生成数据库文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db695324",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import datetime\n",
    "DATABASE = 'data/data.db'\n",
    "def setup_db():\n",
    "    db = sqlite3.connect(DATABASE)\n",
    "    cur = db.cursor()\n",
    "    cur.execute(\"CREATE TABLE IF NOT EXISTS sensorlist(sensorid INTEGER PRIMARY KEY autoincrement, sensorname text,maxvalue int,minvalue int)\")\n",
    "    cur.execute(\"CREATE TABLE IF NOT EXISTS sensorlog(logid INTEGER PRIMARY KEY autoincrement , sensorid int,sensorvalue float,updatetime time)\")\n",
    "    db.commit()\n",
    "    cur.execute(\"SELECT COUNT(*) FROM sensorlist\")\n",
    "    if cur.fetchall()[0][0] == 0:\n",
    "        cur.execute('INSERT INTO sensorlist(sensorid,sensorname,maxvalue,minvalue) VALUES(1,\"温度传感器\",39,5)')\n",
    "        cur.execute('INSERT INTO sensorlist(sensorid,sensorname,maxvalue,minvalue) VALUES(2,\"湿度传感器\",80,20)')\n",
    "        db.commit()\n",
    "    cur.execute(\"SELECT COUNT(*) FROM sensorlog\")\n",
    "    now = datetime.datetime.now()\n",
    "    now = now.strftime('%Y-%m-%d %H:%M:%S')\n",
    "    if cur.fetchall()[0][0] == 0:\n",
    "        cur.execute(\"INSERT INTO sensorlog(logid,sensorid,sensorvalue,updatetime) VALUES(1,1,39,'%s')\"%now)\n",
    "        db.commit()\n",
    "if __name__ == \"__main__\":\n",
    "  setup_db()\n",
    "  print(\"初始化完毕\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0d839482",
   "metadata": {},
   "source": [
    "## 2-开启网页服务器"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a553781",
   "metadata": {},
   "source": [
    "运行如下代码，在行空板开启一个网页服务，开启之后可以浏览器访问行空板IP:端口即可网页查看数据。\n",
    "例如使用USB线连接时可以访问: http://10.1.2.3:8090 \n",
    "> 注：行空板上8080端口默认给siot使用，因此此处使用8090端口"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69c43bfb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * Serving Flask app '__main__' (lazy loading)\n",
      " * Environment: production\n",
      "\u001b[31m   WARNING: This is a development server. Do not use it in a production deployment.\u001b[0m\n",
      "\u001b[2m   Use a production WSGI server instead.\u001b[0m\n",
      " * Debug mode: off\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " * Running on http://10.1.2.3:8000/ (Press CTRL+C to quit)\n"
     ]
    }
   ],
   "source": [
    "# coding= UTF-8\n",
    "import sqlite3\n",
    "import datetime\n",
    "import json\n",
    "from flask import Flask,render_template, request\n",
    "DATABASE = 'data/data.db'\n",
    "app = Flask(__name__)\n",
    "\n",
    "IP=\"10.1.2.3\"\n",
    "PORT=8000\n",
    "\n",
    "@app.route(\"/\")\n",
    "def hello():\n",
    "    db = sqlite3.connect(DATABASE)\n",
    "    cur = db.cursor()\n",
    "    cur.execute(\"SELECT * FROM sensorlog WHERE sensorid =1\")\n",
    "    data = cur.fetchall()\n",
    "    cur.close()\n",
    "    db.close()\n",
    "    temp1 = data[len(data) - 1]\n",
    "    temp=temp1[2]\n",
    "    return render_template('vews.html', data=data,temp=temp)\n",
    "\n",
    "#Get data\n",
    "@app.route(\"/get\",methods=['GET'])\n",
    "def get_data():\n",
    "    sensorid=int(request.args.get('id'))\n",
    "    db = sqlite3.connect(DATABASE)\n",
    "    cur = db.cursor()\n",
    "    cur.execute(\"SELECT * FROM sensorlog WHERE sensorid = %s\"% sensorid)\n",
    "    data = cur.fetchall()\n",
    "    dbsum=len(data)\n",
    "    dset={'sensorid':sensorid}\n",
    "    temp=[]\n",
    "    for i in range(dbsum):\n",
    "        value={}\n",
    "        value['sensorvalue']=data[i][2]\n",
    "        value['updatetime']=data[i][3]\n",
    "        temp.append(value)\n",
    "        #dset['value']={'sensorvalue':data[i-1][2],'updatetime':data[i-1][3]}\n",
    "    dset['value']=temp\n",
    "    djson=json.dumps(dset)\n",
    "    return djson\n",
    "\n",
    "#Adding data\n",
    "@app.route(\"/input\",methods=['POST','GET'])\n",
    "def add_data():\n",
    "    if request.method == 'POST':\n",
    "        sensorid = int(request.form.get('id'))\n",
    "        sensorvalue = float(request.form.get('val'))\n",
    "    else:\n",
    "        sensorid = int(request.args.get('id'))\n",
    "        sensorvalue = float(request.args.get('val')) \n",
    "    nowtime = datetime.datetime.now()\n",
    "    nowtime = nowtime.strftime('%Y-%m-%d %H:%M:%S')\n",
    "    db = sqlite3.connect(DATABASE)\n",
    "    cur = db.cursor()\n",
    "    cur.execute(\"INSERT INTO sensorlog(sensorid,sensorvalue,updatetime) VALUES(%d,%f,'%s')\" %(sensorid,sensorvalue,nowtime) )\n",
    "    db.commit()\n",
    "    cur.execute(\"SELECT * FROM sensorlist where sensorid = %d\"% sensorid)\n",
    "    rv = cur.fetchall()\n",
    "    cur.close()\n",
    "    db.close()\n",
    "    maxrv = rv[0][2]\n",
    "    minrv = rv[0][3]\n",
    "    if sensorvalue > maxrv or sensorvalue < minrv:\n",
    "        return '1'\n",
    "    else:\n",
    "        return '0'\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    app.run(host=IP, port=PORT,debug=False,threaded=True) #jupyter中运行要关闭debug\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3b340c7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59201fe6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
